package cc.mrbird.febs.accounting.controller;

import cc.mrbird.febs.accounting.Constant.CompanyConstant;
import cc.mrbird.febs.accounting.dto.detailCost.DetailCostParam;
import cc.mrbird.febs.accounting.dto.detailCost.DetailTotalDTO;
import cc.mrbird.febs.accounting.dto.importAndExport.ExportParam;
import cc.mrbird.febs.accounting.dto.importAndExport.ImportDataDTO;
import cc.mrbird.febs.accounting.dto.importAndExport.ImportExcelData;
import cc.mrbird.febs.accounting.dto.importAndExport.ImportParam;
import cc.mrbird.febs.accounting.dto.standardSub.ExportFlagDTO;
import cc.mrbird.febs.accounting.dto.standardSub.SingleExportDTO;
import cc.mrbird.febs.accounting.dto.standardSub.SubExportData;
import cc.mrbird.febs.accounting.entity.*;
import cc.mrbird.febs.accounting.service.IDetailCostService;
import cc.mrbird.febs.accounting.service.IProjectService;
import cc.mrbird.febs.accounting.service.impl.CompanyProjectServiceImpl;
import cc.mrbird.febs.system.entity.Role;
import cc.mrbird.febs.system.mapper.RoleMapper;
import cc.mrbird.febs.utils.*;
import cc.mrbird.febs.common.annotation.ControllerEndpoint;
import cc.mrbird.febs.common.controller.BaseController;
import cc.mrbird.febs.common.entity.FebsConstant;
import cc.mrbird.febs.common.entity.FebsResponse;
import cc.mrbird.febs.common.entity.QueryRequest;
import cc.mrbird.febs.common.exception.FebsException;
import cc.mrbird.febs.common.utils.FebsUtil;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.sun.org.apache.xpath.internal.operations.Bool;
import com.wuwenze.poi.ExcelKit;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.stereotype.Controller;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * Controller
 *
 * @author baoyinlei
 * @date 2021-09-28 23:27:01
 */
@Slf4j
@Validated
@Controller
@RequiredArgsConstructor
public class DetailCostController extends BaseController {

    @Resource
    private IDetailCostService detailCostService;
    @Resource
    private IProjectService projectService;

    @Resource
    private CompanyProjectServiceImpl companyProjectService;
    @Resource
    private RoleMapper roleMapper;
    @Resource DetailCostUtil detailCostUtil;

    @GetMapping(FebsConstant.VIEW_PREFIX + "detailCost")
    public String detailCostIndex() {
        return FebsUtil.view("detailCost/detailCost");
    }

    @PostMapping("detailCost/save")
    @ResponseBody
    @RequiresPermissions("detailCost:save")
    public FebsResponse saveDetailCost(@ModelAttribute DetailCostParam detailCostParam) {
        if (Objects.isNull(detailCostParam)
                || Objects.isNull(detailCostParam.getCompanyId())
                || Objects.isNull(detailCostParam.getProjectId())
                || Objects.isNull(detailCostParam.getYearMonth())) {
            return new FebsResponse().fail().message("参数不正确");
        }
        if (StringUtils.isNotBlank(detailCostParam.getDetailCostListStr())) {
            List<DetailCost> detailCosts = JSONObject.parseArray(detailCostParam.getDetailCostListStr(), DetailCost.class);
            List<DetailCost> collect = detailCosts.stream().filter(e -> Objects.nonNull(e.getCost())).collect(Collectors.toList());
            detailCostParam.setDetailCostList(collect);
        }
        detailCostService.saveDetailCost(detailCostParam);
        return new FebsResponse().success();
    }

    @PostMapping("detailCost/import")
    @ResponseBody
    @RequiresPermissions("data:import")
    public FebsResponse importData(@RequestParam("uploadFile") MultipartFile multipartFile, ImportParam importParam) throws IOException {
        if (StringUtils.isBlank(importParam.getYear())) {
            return new FebsResponse().fail().message("年份不能为空");
        }
        InputStream inputStream = multipartFile.getInputStream();
        ImportExcelData importExcelData = new ImportExcelData();
        try {
            importExcelData = this.detailCostService.importExcel(inputStream, importParam);
        } catch (Exception e) {
            CodeConstant.isImport = false;
            return new FebsResponse().fail().message("导入失败，请联系管理员");
        }
        CodeConstant.isImport = false;
        if (importExcelData.getErrorTemplate() || importExcelData.getHasErrorMonth() != -1) {
            return new FebsResponse().fail().message(importExcelData.getImportMessage());
        } else if (importExcelData.getErrorProjectList().size() == 0 && importExcelData.getErrorSubjectList().size() == 0) {
            return new FebsResponse().success().message(importExcelData.getImportMessage());
        } else {
            return new FebsResponse().fail().message(importExcelData.getImportMessage());
        }
    }

    private List<ImportDataDTO> explainExcel(InputStream inputStream) throws IOException {
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        XSSFRow row = sheetAt.getRow(0);
        Set<String> keySets = Sets.newLinkedHashSet(Lists.newArrayList("所属项目", "科目明细", "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月", "差额"));
        Map<String, Integer> keyIndexMap = new LinkedHashMap<>();
        for (int i = 0; i < row.getLastCellNum(); i++) {
            XSSFCell cell = row.getCell(i);
            if (Objects.isNull(cell)) {
                continue;
            }
            String stringCellValue = cell.getStringCellValue();
            if (keySets.contains(stringCellValue.trim())) {
                keyIndexMap.put(stringCellValue.trim(), i);
            }
        }

        Map<String, ImportDataDTO> importDataDTOMap = new LinkedHashMap<>();
        for (int i = 1; i < sheetAt.getLastRowNum() + 1; i++) {
            XSSFRow dataRow = sheetAt.getRow(i);
            XSSFCell projectCell = dataRow.getCell(keyIndexMap.get("所属项目"));
            String projectName = projectCell.getStringCellValue().trim();
            XSSFCell subjectCell = dataRow.getCell(keyIndexMap.get("科目明细"));
            String subjectName = subjectCell.getStringCellValue().trim();
            if (StringUtils.isBlank(projectName) || StringUtils.isBlank(subjectName)) {
                continue;
            }
            ImportDataDTO importDataDTO;
            if (importDataDTOMap.containsKey(projectName.concat(subjectName))) {
                importDataDTO = importDataDTOMap.get(projectName.concat(subjectName));
            } else {
                importDataDTO = new ImportDataDTO();
                importDataDTO.setProjectName(projectName);
                importDataDTO.setDetailSubject(subjectName);
                importDataDTO.setValueMonthMap(new HashMap<>());
                importDataDTOMap.put(projectName.concat(subjectName), importDataDTO);
            }
            for (Map.Entry<String, Integer> entry : keyIndexMap.entrySet()) {
                String key = entry.getKey();
                Integer index = entry.getValue();
                if ("所属项目".equals(key) || "科目明细".equals(key)) {
                    continue;
                }
                XSSFCell cell = dataRow.getCell(index);
                if (Objects.isNull(cell)) {
                    continue;
                }
                Map<String, Double> valueMonthMap = importDataDTO.getValueMonthMap();
                Double beforValue = 0d;
                if (valueMonthMap.containsKey(key)) {
                    beforValue = valueMonthMap.get(key);
                }
                cell.setCellType(CellType.STRING);
                if (StringUtils.isNotBlank(cell.getStringCellValue().trim())) {
                    boolean number = isNumber(cell.getStringCellValue().trim());
                    if (!number) {
                        throw new FebsException("表格中的金额填写不规范");
                    }
                }
                valueMonthMap.put(key, beforValue + Double.parseDouble(cell.getStringCellValue().trim()));
            }
        }
        List<ImportDataDTO> importDataDTOS = new ArrayList<>();
        for (Map.Entry<String, ImportDataDTO> entry : importDataDTOMap.entrySet()) {
            importDataDTOS.add(entry.getValue());
        }
        return importDataDTOS;
    }

    private boolean isNumber(String str) {
        Pattern pattern = Pattern.compile("^-?([0-9]+|[0-9]{1,3}(,[0-9]{3})*)(.[0-9]{1,2})?$");
        Matcher match = pattern.matcher(str);
        return match.matches();
    }

    @GetMapping("detailCost")
    @ResponseBody
    @RequiresPermissions("detailCost:list")
    public FebsResponse getAllDetailCosts() {
        return new FebsResponse().success().data(detailCostService.findDetailCosts());
    }

    @GetMapping("detailCost/queryDetailCost")
    @ResponseBody
    //@RequiresPermissions("project:list")
    public FebsResponse queryDetailCost(Long projectId, Long companyId, String yearMonth) {
        if (Objects.isNull(projectId) || Objects.isNull(companyId)) {
            return new FebsResponse().fail().message("传入的参数有误");
        }
        if (yearMonth.indexOf("-") < 3) {
            return new FebsResponse().fail().message("传入的参数有误");
        }
        Map<String, Object> map = this.detailCostService.queryDetailCost(companyId, projectId, yearMonth);
        return new FebsResponse().success().data(map);
    }

    @GetMapping("detailCost/exportMainTable")
    public void exportMainTable(HttpServletResponse response, ExportParam exportParam) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        System.out.println("----------");
        System.out.println(exportParam);
        String filePath = exportParam.getYear().concat("主表").concat(".xlsx");
        this.detailCostService.exportMainTable(workbook, exportParam);
        response.reset();//清除buffer缓存
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + new String(filePath.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));// 定义文件名
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Expires", " 0");

        try {
            OutputStream output;
            output = response.getOutputStream();
            BufferedOutputStream bufferOutput = new BufferedOutputStream(output);
            bufferOutput.flush();
            workbook.write(bufferOutput);
            bufferOutput.close();
        } catch (Exception e) {
            System.out.println("主表导出失败!");
        }
    }

    @GetMapping("detailCost/exportSingle")
    // 获取到和前端相对应的detailCost/exportSingle params中params所对应的各字段名字，year和params中的name:year是同一个
    // excelType：1:第三方，2：含管理成本及补助减免
    public void download(HttpServletResponse response, @RequestParam("year") String year, @RequestParam("month") String month,
                         @RequestParam("projectId") String projectId, @RequestParam("excelType") Integer excelType,
                         @RequestParam("orderByPro") Integer orderByPro) throws IOException {
        // 创建一个xlsx类型的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //List<SingleExportDTO> singleExportDTOS = this.detailCostService.querySingleExportData(projectId, year);
        // 将从params中获取到的month以数组的形式存入字符数组months中
        List<String> months = Arrays.asList(month.split(","));
        // 创建一个空字符数组 yearMonths
        List<String> yearMonths = new ArrayList<>();
        // 使yearMonths里面存的是规范为yy-mm的年月（如2022-04）
        for (String e : months) {
            if (Integer.parseInt(e) < 10) {
                yearMonths.add(year + "-0" + e);
            } else {
                yearMonths.add(year + "-" + e);
            }
        }
        yearMonths.add(year + "-" + "balance");
        // Arrays.asList(projectId.split(","))是选中的project_id,指把选中的项目所对应的Id放入字符数组中，比如（选择浙江丽水的，对应的id是81，把表里的所有数据放入字符数组中)
        // this.projectService.getProjectByIds是去数据库里找选中的这个project_id对应的数据
        List<String> ids = Arrays.asList(projectId.split(","));
        List<Project> projects_tmp = this.projectService.getProjectByIds(ids);
        List<Project> projects = new ArrayList<>();
        // 将项目按照选择的顺讯排序
        if (orderByPro == 1) {
            for (String id : ids) {
                projects.add(projects_tmp.stream().filter(e->e.getId().toString().equals(id)).findFirst().orElse(null));
            }
        } else {
            projects = projects_tmp;
        }
        StringBuilder flag = new StringBuilder();
        StringBuilder insuranceError = new StringBuilder();
        for (Project project : projects) {

            ExportFlagDTO exportFlagDTO = detailCostUtil.getMergedSingleExport(project.getId(), year, yearMonths);
            // 对不同公司的项目数据按照项目进行合并
            List<SingleExportDTO> mergedList = exportFlagDTO.getSingleExportDTOS();
            // 调整合并后的部分计算
            mergedList = detailCostUtil.adjustResult(mergedList);

            if (exportFlagDTO.getFlag()==1) {
                flag.append(projectId).append("/1-");
            }
            //填充不存在的字段
            this.detailCostService.fillingField(mergedList, project.getId(), yearMonths);
            boolean result = extracted(workbook, project, mergedList, year, months, excelType);
            if (!result) {
                flag.append(project.getId()).append("/2-");
            }
            if (exportFlagDTO.getInsuranceError()) {
                insuranceError.append(project.getId()).append("-");
            }
        }
        String filePath = year.concat("分表汇总表").concat(".xlsx");
        response.reset();//清除buffer缓存
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + new String(filePath.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));// 定义文件名
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Expires", " 0");
//        response.setStatus(220);
        if(!flag.toString().equals("")) {
            response.setHeader("flag", flag.toString());
        }
        if (!"".equals(insuranceError.toString())) {
            response.setHeader("insuranceError", insuranceError.toString());
        }
        try {
            OutputStream output;
            output = response.getOutputStream();
            BufferedOutputStream bufferOutput = new BufferedOutputStream(output);
            bufferOutput.flush();
            workbook.write(bufferOutput);
            bufferOutput.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private boolean extracted(XSSFWorkbook workbook, Project project, List<SingleExportDTO> singleExportDTOS, String year,
                           List<String> months, Integer excelType) {
        int rowNum, t = 1;
        XSSFSheet spreadsheet = workbook.createSheet(project.getProjectName());  // 创建一个表
        Map<String, XSSFCellStyle> styleMap = StyleMap.getStyleMap(workbook);    // 分表样式
        XSSFCellStyle defaultStyle = StyleMap.getDefaultStyle(workbook);         // 分表默认样式
        //设置标题
        setExcelTitle(spreadsheet, workbook, project.getProjectName(), year, 15 - (12 - months.size()));
        //设置子标题
        setSubTitle(spreadsheet, workbook);
        //具体数据 去除两行标准, 同时设置边框和样式，防止创建样式越界
        rowNum = setDetailData(spreadsheet, workbook, singleExportDTOS, year, excelType, project.getProjectCity(),
                styleMap, defaultStyle);
        if (rowNum < 0) {
            t = -1;
            rowNum = (-1) * rowNum;
        }
        //设置列宽
        setSheetWidth(spreadsheet);
        //添加所有单元格的边框
        //setAllBorder(spreadsheet, workbook, 16);
        //装饰颜色
        //setDecorationColor(spreadsheet, workbook);
        //设置主科目样式
        setMainSubStyle(spreadsheet, workbook);
        //删除指定列,根据选择的月份
        deleteColumns(spreadsheet, months);
        // 添加本年合计计算公式
        setYearTotal(spreadsheet, workbook, rowNum, months.size(), styleMap, defaultStyle);
        // 添加批注行
        String lastComment = excelType == 1 ? project.getComment() : project.getComment2();
        if (lastComment != null) setLastComment(spreadsheet, workbook, lastComment, rowNum, 15 - (12 - months.size()));
        return t == 1;
    }

    @ResponseBody
    @GetMapping("detailCost/getUserPermission")
    public FebsResponse getUserPermission() {
        Integer[] hasList = {1, 9};
        boolean hasPermission = false;
        for (Integer integer : hasList) {
            if (FebsUtil.getCurrentUser().getUserId().intValue() == integer) {
                hasPermission = true;
            }
        }
        return new FebsResponse().success().data(hasPermission);
    }

    private void setSheetWidth(XSSFSheet spreadsheet) {
        spreadsheet.setColumnWidth(0, 252 * 10 + 323);
        spreadsheet.setColumnWidth(1, 252 * 34 + 323);
        for (int i = 2; i < 15; i++) {
            spreadsheet.setColumnWidth(i, 252 * 13 + 323);
        }
        spreadsheet.setColumnWidth(15, 252 * 13 + 323);
    }

    private Integer setDetailData(XSSFSheet spreadsheet, XSSFWorkbook workbook, List<SingleExportDTO> singleExportDTOS,
                                  String year, Integer excelType, String projectCity, Map<String, XSSFCellStyle> styleMap, XSSFCellStyle defaultStyle) {
        Set<String> mainSet = new HashSet<>();
        //所有值放在map中为后面提供计算

        // 过滤留下项目singleExportDTOs中费用不为0的，把明细科目当做key费用当做value放入codeValueMap里
        Map<String, BigDecimal> codeValueMap = singleExportDTOS.stream().filter(e -> Objects.nonNull(e.getCost())).
                collect(Collectors.toMap(k -> k.getSubCode() + "==" + k.getYearMonth(), SingleExportDTO::getCost));
        List<MainExportData> mainExportDataList = new ArrayList<>();
        for (SingleExportDTO singleExportDTO : singleExportDTOS) {
            if (!mainSet.contains(singleExportDTO.getMainCode())) {
                MainExportData mainExportData = new MainExportData();
                mainExportData.setMainCode(singleExportDTO.getMainCode());
                mainExportData.setMainName(singleExportDTO.getMainName());
                mainExportData.setMainOrder(singleExportDTO.getMainOrder());
                mainExportDataList.add(mainExportData);
                mainSet.add(singleExportDTO.getMainCode());
            }
        }
        // mainExportDataList里放的是所有指定的明细科目对应的大类的Code， Name，Order
        mainExportDataList.sort(Comparator.comparing(MainExportData::getMainOrder));
        for (MainExportData mainExportData : mainExportDataList) {
            String mainCode = mainExportData.getMainCode();
            // 在所有详细科目中过滤出来所有属于当前大类的科目以列表的形式存入List
            List<SingleExportDTO> collect = singleExportDTOS.stream().filter(e -> mainCode.equals(e.getMainCode())).collect(Collectors.toList());
            Set<String> subSet = new HashSet<>();
            //去除两个标准和收入差额
            subSet.add(CodeConstant.yearEndAccrualStandardCode);
            subSet.add(CodeConstant.otherWelfareStandardCode);
            subSet.add(CodeConstant.incomeDifference);
            List<String> thirdCodes = new ArrayList<>();  // 去除与第三方和收入有关科目code
            thirdCodes.add(CodeConstant.thirdPartCode);
            thirdCodes.add(CodeConstant.selfIncomeCode);
            thirdCodes.add(CodeConstant.thirdCostSumCode);
            thirdCodes.add(CodeConstant.pendingPaymentCode);
            List<SubExportData> subExportDataList = new ArrayList<>();
            for (SingleExportDTO singleExportDTO : collect) {
                if (!subSet.contains(singleExportDTO.getSubCode())) {
                    // 去除与第三方和收入有关科目code
                    if (thirdCodes.contains(singleExportDTO.getSubCode()) && (singleExportDTO.getCost() == null ||
                            singleExportDTO.getCost().equals(BigDecimal.ZERO))) {
                        continue;
                    }
                    SubExportData subExportData = new SubExportData();
                    subExportData.setSubCode(singleExportDTO.getSubCode());
                    subExportData.setSubName(singleExportDTO.getSubName());
                    // collect1 里面存的是在所有选中的详细科目中找到符合条件(singleExportDTO这一大类里面的subCode)的这一详细科目的信息
                    List<SingleExportDTO> collect1 = singleExportDTOS.stream().filter(e -> singleExportDTO.getSubCode().equals(e.getSubCode())).collect(Collectors.toList());
                    Map<String, Integer> estimateMap = new HashMap<>();    // 对应年月，预估支出
                    Map<String, Double> valueMap = new HashMap<>();       // 对应的是年月，花费
                    Map<String, String> remarkMap = new HashMap<>();     // 对应年月，标记
                    for (SingleExportDTO singleExportDTO1 : collect1) {
                        if (StringUtils.isNotBlank(singleExportDTO1.getYearMonth())) {
                            valueMap.put(singleExportDTO1.getYearMonth(), singleExportDTO1.getCost().doubleValue());
                            estimateMap.put(singleExportDTO1.getYearMonth(), singleExportDTO1.getEstimate());
                            if (singleExportDTO1.getRemark() != null && !singleExportDTO1.getRemark().equals("")) {
                                remarkMap.put(singleExportDTO1.getYearMonth(), singleExportDTO1.getRemark());
                            }
                        }
                    }
                    subExportData.setValueMap(valueMap);
                    subExportData.setEstimateMap(estimateMap);
                    subExportData.setRemarkMap(remarkMap);
                    subSet.add(singleExportDTO.getSubCode());
                    subExportDataList.add(subExportData);
                }
            }
            mainExportData.setSubExportDataList(subExportDataList);
        }

        int startRow = 2;
        int t=1;
        for (int i = 0; i < mainExportDataList.size(); i++) {
            MainExportData mainExportData = mainExportDataList.get(i);
            String mainName = mainExportData.getMainName();
            for (int j = 0; j < mainExportData.getSubExportDataList().size(); j++) {
                SubExportData subExportData = mainExportData.getSubExportDataList().get(j);

                String subName = subExportData.getSubName();
                XSSFRow row = spreadsheet.createRow((short) (startRow + j));
                XSSFCell firstCell = row.createCell(0);
                firstCell.setCellValue(mainName);
                XSSFCell secondCell = row.createCell(1);
                secondCell.setCellValue(subName);              //设置内容
                firstCell.setCellStyle(styleMap.getOrDefault(subName, defaultStyle));  // 设置单元格样式
                secondCell.setCellStyle(styleMap.getOrDefault(subName, defaultStyle)); // 设置单元格样式

                Map<String, Double> valueMap = subExportData.getValueMap();
                Map<String, Integer> estimateMap = subExportData.getEstimateMap();
                Map<String, String> remarkMap = subExportData.getRemarkMap();
                Map<String, Integer> dataIndex = getDataIndex();

                if (Objects.nonNull(valueMap)) {
                    Set<Map.Entry<String, Double>> entries = valueMap.entrySet();
                    for (Map.Entry<String, Double> e : entries) {
                        String s = e.getKey().split("-")[1];
                        Integer integer = dataIndex.get(s);
                        // 在row行integer列创建单元格
                        XSSFCell valueCell = row.createCell(integer);
                        Integer estimate = estimateMap.get(e.getKey());
                        if (Objects.nonNull(estimate) && estimate == 1) {
                            XSSFCellStyle style = styleMap.getOrDefault(subName, defaultStyle);
                            XSSFCellStyle newStyle = workbook.createCellStyle();
                            newStyle.cloneStyleFrom(style);
                            setRedCellStyle(workbook, newStyle);
                            valueCell.setCellStyle(newStyle);
                        } else {
                            valueCell.setCellStyle(styleMap.getOrDefault(subName, defaultStyle));
                        }
                        valueCell.setCellValue(e.getValue());

                        if (remarkMap.containsKey(e.getKey())) {
                            String remark = remarkMap.get(e.getKey()).trim();
                            setCellComment(spreadsheet, workbook, startRow + j, integer, remark);
                        }
                    }
                }
                // 添加空白单元格
                for (int k = 2; k <= 14; k++) {
                    XSSFCell cell = row.getCell(k);
                    if (Objects.isNull(cell)) {
                        cell = row.createCell(k);
                        cell.setCellStyle(styleMap.getOrDefault(subName, defaultStyle));
                    }
                }
            }
            // 合并第一列单元格
            CellRangeAddress cellRangeMain = new CellRangeAddress(startRow, startRow + mainExportData.getSubExportDataList().size() - 1, 0, 0);
            spreadsheet.addMergedRegion(cellRangeMain);

            startRow += mainExportData.getSubExportDataList().size();
            //插入利润 自有驻场，第三方职场
            if (mainExportData.getMainCode().equals(CodeConstant.taxCode)) {
                startRow = createExtraCell(spreadsheet, year, codeValueMap, startRow, 1, excelType, styleMap, defaultStyle);
                if (startRow < 0) {
                    t = -1;
                    startRow = (-1) * startRow;
                }
                if (excelType == 1) {
                    break;
                }
            }
            if (mainExportData.getMainCode().equals(CodeConstant.manageCost)) {
                startRow = createExtraCell(spreadsheet, year, codeValueMap, startRow, 2, excelType, styleMap, defaultStyle);
            }
            if (mainExportData.getMainCode().equals(CodeConstant.reliefItems)) {
                startRow = createExtraCell(spreadsheet, year, codeValueMap, startRow, 3, excelType, styleMap, defaultStyle);
                // 按照需求暂时去掉
//                if (projectCity!=null && projectCity.trim().equals("杭州")) {
//                    startRow = createExtraCell(spreadsheet, year, codeValueMap, startRow, 4, styleMap, defaultStyle);
//                } else {
//                    startRow = createExtraCell(spreadsheet, year, codeValueMap, startRow, 5, styleMap, defaultStyle);
//                }
            }
        }
        return startRow * t;
    }

    private Integer createExtraCell(XSSFSheet spreadsheet, String year, Map<String, BigDecimal> codeValueMap, int startRow, Integer type,
                                    Integer excelType, Map<String, XSSFCellStyle> styleMap, XSSFCellStyle style) {
        int tt = 1;
        List<DetailTotalDTO> standardSubs = YearMonthMapUtils.getStandardSubs(excelType);
        List<DetailTotalDTO> firstTotal = standardSubs.stream()
                .filter(e -> Objects.equals(e.getType(), type) && e.getExcelType() <= excelType)
                .collect(Collectors.toList());
        int passRowNum = 0;
        for (int j = 0; j < firstTotal.size(); j++) {
            // passRowNum = 0;
            DetailTotalDTO detailTotalDTO = firstTotal.get(j);
            Set<String> ruleCodeSet = getRuleCodeSet(detailTotalDTO.getCalculateStr());
            //  如果第三方支出合计和第三方收入合计都为0，则去除第三方职场

            // not see
            boolean ifPass = false;
            if (detailTotalDTO.getName().equals("第三方职场")) {
                ifPass = true;
                for (String month : YearMonthMapUtils.monthList) {
                    for (String ruleCode : ruleCodeSet) {
                        String key = ruleCode + "==" + year + "-" + month;
                        BigDecimal value = codeValueMap.get(key);
                        if (!Objects.isNull(value) && !value.equals(BigDecimal.ZERO)) {
                            ifPass = false;
                        }
                    }
                }
            }
            if (ifPass) {
                passRowNum++;
                continue;
            }
            // not
            int nowRow = startRow + j - passRowNum;
            XSSFRow row = spreadsheet.createRow((short) (nowRow));
            XSSFCell firstCell = row.createCell(0);
            firstCell.setCellValue(detailTotalDTO.getName());
            XSSFCell secondCell = row.createCell(1);
            secondCell.setCellValue(detailTotalDTO.getName());
            firstCell.setCellStyle(styleMap.getOrDefault(detailTotalDTO.getName(), style));
            secondCell.setCellStyle(styleMap.getOrDefault(detailTotalDTO.getName(), style));
            CellRangeAddress cellRangeMain = new CellRangeAddress(nowRow, nowRow, 0, 1);
            spreadsheet.addMergedRegion(cellRangeMain);

            ScriptEngine jse = new ScriptEngineManager().getEngineByName("JavaScript");
            int startCell = 2;
            // not see
            String rule = detailTotalDTO.getCalculateStr();
            for (String month : YearMonthMapUtils.monthList) {
                String realRule = rule;
                for (String ruleCode : ruleCodeSet) {
                    String key = ruleCode + "==" + year + "-" + month;
                    BigDecimal value = codeValueMap.get(key);
                    if (Objects.isNull(value)) {
                        value = new BigDecimal(0);
                    }
                    realRule = realRule.replaceAll("【" + ruleCode + "】", Double.toString(value.doubleValue()));
                }
                //计算公式
                Double reuslt = 0d;
                try {
                    reuslt = (Double) jse.eval(realRule);
                } catch (ScriptException e) {
                    e.printStackTrace();
                }
                if ( detailTotalDTO.getName().equals("第三方职场") && reuslt < 0 && type == 1) {
                    tt = -1;
                }
                reuslt = Double.parseDouble(String.format("%.2f", reuslt));
                XSSFCell calculateCell = row.createCell(startCell);
                startCell++;
                calculateCell.setCellValue(reuslt);
                calculateCell.setCellStyle(styleMap.getOrDefault(detailTotalDTO.getName(), style));
            }
        }
        startRow += firstTotal.size() - passRowNum;
        return startRow * tt;
    }

    private Set<String> getRuleCodeSet(String rule) {
        Set<String> codeSet = new HashSet<>();
        String pattern = "(?<=【)([^【】]*)(?=】)";
        Pattern r = Pattern.compile(pattern);
        Matcher m = r.matcher(rule);
        while (m.find()) {
            codeSet.add(m.group());
        }
        return codeSet;
    }

    private void setExcelTitle(XSSFSheet spreadsheet, XSSFWorkbook workbook, String projectName, String year, Integer lastCol) {
        XSSFRow row = spreadsheet.createRow((short) 0);
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 18); // 字体大小
        fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(fontTitle);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, lastCol);
        spreadsheet.addMergedRegion(cellRangeAddress);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("杭州".concat(CompanyConstant.companyName).concat("科技有限公司").concat(year).concat("内部成本核算表")
                .concat("(").concat(projectName).concat(")"));
        cell.setCellStyle(titleStyle);
        row.setHeightInPoints(24);
    }

    private void setSubTitle(XSSFSheet spreadsheet, XSSFWorkbook workbook) {
        XSSFRow row = spreadsheet.createRow((short) 1);
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 14); // 字体大小
        fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(fontTitle);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
        titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
        titleStyle.setBorderTop(BorderStyle.THIN);//上边框
        titleStyle.setBorderRight(BorderStyle.THIN);//右边框
        List<String> subTitle = getSubTitle();
        for (int i = 0; i < subTitle.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(subTitle.get(i));
            cell.setCellStyle(titleStyle);
            row.setHeightInPoints(31);
        }
    }

    private XSSFCellStyle addBorder(XSSFCellStyle borderStyle, String code) {
        borderStyle.setFillForegroundColor(setForeColor(code));
        borderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        borderStyle.setBorderBottom(BorderStyle.THIN); //下边框
        borderStyle.setBorderLeft(BorderStyle.THIN);//左边框
        borderStyle.setBorderTop(BorderStyle.THIN);//上边框
        borderStyle.setBorderRight(BorderStyle.THIN);//右边框
        return borderStyle;
    }

    private XSSFColor setForeColor(String code) {
        Map<String, java.awt.Color> getColor = YearMonthMapUtils.getColor;
        java.awt.Color color = getColor.get(code);
        if (Objects.nonNull(color)) {
            return new XSSFColor(color, new DefaultIndexedColorMap());
        }
        return new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap());
    }

    private List<String> getSubTitle() {
        return Lists.newArrayList("总科目",
                "明细科目",
                "1月",
                "2月",
                "3月",
                "4月",
                "5月",
                "6月",
                "7月",
                "8月",
                "9月",
                "10月",
                "11月",
                "12月",
                "差额",
                "本年累计"
        );
    }

    private Map<String, Integer> getDataIndex() {
        Map<String, Integer> dataIndexMap = new HashMap<>();
        dataIndexMap.put("01", 2);
        dataIndexMap.put("02", 3);
        dataIndexMap.put("03", 4);
        dataIndexMap.put("04", 5);
        dataIndexMap.put("05", 6);
        dataIndexMap.put("06", 7);
        dataIndexMap.put("07", 8);
        dataIndexMap.put("08", 9);
        dataIndexMap.put("09", 10);
        dataIndexMap.put("10", 11);
        dataIndexMap.put("11", 12);
        dataIndexMap.put("12", 13);
        dataIndexMap.put("balance", 14);
        return dataIndexMap;
    }

    private String getConnectRemark(List<String> remarkList) {
        StringBuilder builder = new StringBuilder();
        if (Objects.nonNull(remarkList) && !remarkList.isEmpty()) {
            remarkList.forEach(e -> {
                if (StringUtils.isNotBlank(e)) {
                    builder.append(e).append("\r\n");
                }
            });
        }
        return builder.toString();
    }

    private void setRedCellStyle(XSSFWorkbook workbook, XSSFCellStyle redCellStyle) {
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setColor(new XSSFColor(new java.awt.Color(255, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
        redCellStyle.setFont(fontTitle);
    }

    private void setAllBorder(XSSFSheet spreadsheet, XSSFWorkbook workbook1, int lastCol) {
        int lastRowNum = spreadsheet.getLastRowNum();
        int lastIndex = lastCol;
        XSSFWorkbook workbook = new XSSFWorkbook();
        for (int i = 1; i < lastRowNum + 1; i++) {
            XSSFRow row = spreadsheet.getRow(i);
            for (int j = 0; j < lastIndex; j++) {
                XSSFCell cell = row.getCell(j);
                if (Objects.isNull(cell)) {
                    cell = row.createCell(j);
                }
                XSSFCellStyle cellStyle1 = workbook.createCellStyle();
                cellStyle1.cloneStyleFrom(cell.getCellStyle());
                cellStyle1.setBorderBottom(BorderStyle.THIN); //下边框
                cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
                cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
                cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
                cell.setCellStyle(cellStyle1);
            }
        }
    }

    private void setDecorationColor(XSSFSheet sheet, XSSFWorkbook workbook1) {
        int lastRowNum = sheet.getLastRowNum();
        XSSFWorkbook workbook = new XSSFWorkbook();
        for (int i = 2; i < lastRowNum + 1; i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFCell subDetailCell = row.getCell(1);
            String stringCellValue = subDetailCell.getStringCellValue();
            for (int j = 1; j < 16; j++) {
                XSSFCellStyle cellStyle = workbook.createCellStyle();
                XSSFCell cell = row.getCell(j);
                cellStyle.cloneStyleFrom(cell.getCellStyle());
                cellStyle.setFillForegroundColor(setSubTitleColor(stringCellValue.trim()));
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private XSSFColor setSubTitleColor(String subTitle) {
        Map<String, java.awt.Color> getColor = YearMonthMapUtils.getSubDetailColor;
        java.awt.Color color = getColor.get(subTitle);
        if (Objects.nonNull(color)) {
            return new XSSFColor(color, new DefaultIndexedColorMap());
        }
        return new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap());
    }

    private void setMainSubStyle(XSSFSheet sheet, XSSFWorkbook workbook) {
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 2; i < lastRowNum + 1; i++) {
            XSSFRow row = sheet.getRow(i);
//            if (row == null) continue;
            XSSFCell mainDetailCell = row.getCell(0);
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.cloneStyleFrom(mainDetailCell.getCellStyle());
            XSSFFont fontTitle = workbook.createFont();
            fontTitle.setFontHeightInPoints((short) 12); // 字体大小
            fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
            fontTitle.setFontName("宋体");
            fontTitle.setBold(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cellStyle.setFont(fontTitle);
            String stringCellValue = mainDetailCell.getStringCellValue();
            cellStyle.setFillForegroundColor(setSubTitleColor(stringCellValue));
            switch (stringCellValue) {
                case "收入":
                    break;
                case "费用":
                    break;
                case "成本":
                    break;
                case "税费":
                    break;
                case "毛利润（预算表）":
                    row.setHeightInPoints(24);
                    break;
                case "其中：自有（驻场）":
                    row.setHeightInPoints(24);
                    break;
                case "第三方职场":
                    row.setHeightInPoints(24);
                    break;
                case "毛利润（按实际社保和工会费）":
                    row.setHeightInPoints(24);
                    break;
                case "管理成本":
                    break;
                case "毛利润2":
                    row.setHeightInPoints(24);
                case "补助或减免项":

                    break;
                case "营业利润":
                    row.setHeightInPoints(21);
                    break;
                case "内部报表成本（杭州项目）":
                    break;
                case "内部报表成本（非杭州项目）":
                    break;
                default:
                    break;
            }
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            mainDetailCell.setCellStyle(cellStyle);
        }
    }

    private void createMainTable(XSSFWorkbook workbook, List<SingleExportDTO> singleExportDTOS, String year) {
        XSSFSheet spreadsheet = workbook.createSheet("内部报表权责制");
        //设置标题
        createMainTable(spreadsheet, workbook, year);
        //设置月份,子标题
        createMainMonth(spreadsheet, workbook);
        //设置数据
        createMainData(spreadsheet, workbook, singleExportDTOS, year);
        //添加所有单元格的边框
        setAllBorder(spreadsheet, workbook, 28);
        //设置列宽
        setColWidth(spreadsheet);
        //上色
        setMainTableColor(spreadsheet, workbook);
        //合计单独设置成蓝色
        setTotalColor(spreadsheet, workbook);
    }

    private void setTotalColor(XSSFSheet spreadsheet, XSSFWorkbook workbook) {
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(154, 203, 255), new DefaultIndexedColorMap());
        for (int i = 3; i < spreadsheet.getLastRowNum() + 1; i++) {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            XSSFRow row = spreadsheet.getRow(i);
            XSSFCell cell = row.getCell(1);
            String stringCellValue = cell.getStringCellValue().trim();
            if ("第三方收入合计".equals(stringCellValue) || "费用合计".equals(stringCellValue)
                    || "成本合计".equals(stringCellValue)
                    || "合计".equals(stringCellValue)
                    || "管理成本合计".equals(stringCellValue)
                    || "补助/减免合计".equals(stringCellValue)) {
                for (int j = 2; j < 28; j++) {
                    XSSFCell cell1 = row.getCell(j);
                    cellStyle.cloneStyleFrom(cell1.getCellStyle());
                    cellStyle.setFillForegroundColor(xssfColor);
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cell1.setCellStyle(cellStyle);
                }
            }
        }
    }

    private void setMainTableColor(XSSFSheet spreadsheet, XSSFWorkbook workbook) {
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(154, 203, 255), new DefaultIndexedColorMap());
        XSSFRow row = spreadsheet.getRow(2);
        for (int i = 1; i < 14; i++) {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            XSSFCell cell = row.getCell(2 * i);
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            cellStyle.setFillForegroundColor(xssfColor);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(cellStyle);
        }
        for (int i = 3; i < spreadsheet.getLastRowNum() + 1; i++) {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            XSSFRow row1 = spreadsheet.getRow(i);
            XSSFCell cell = row1.getCell(1);
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            cellStyle.setFillForegroundColor(xssfColor);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(cellStyle);
        }
    }

    private void setColWidth(XSSFSheet spreadsheet) {
        spreadsheet.setColumnWidth(0, 252 * 6 + 323);
        spreadsheet.setColumnWidth(1, 252 * 51 + 323);
        for (int i = 2; i < 28; i++) {
            spreadsheet.setColumnWidth(i, 252 * 17 + 323);
        }
        XSSFRow row = spreadsheet.getRow(2);
        row.setHeightInPoints(30);
    }

    private void createMainData(XSSFSheet spreadsheet, XSSFWorkbook workbook, List<SingleExportDTO> singleExportDTOS, String year) {
        List<SingleExportDTO> standardSubjects = this.detailCostService.querySubjectDetail();
        List<String> mainCodeList = standardSubjects.stream().map(SingleExportDTO::getMainCode).distinct().collect(Collectors.toList());
        Map<String, List<SingleExportDTO>> mianCoedSizeMap = standardSubjects.stream().collect(Collectors.groupingBy(SingleExportDTO::getMainCode));
        int startIndex = 3;
        for (int i = 0; i < standardSubjects.size(); i++) {
            SingleExportDTO singleExportDTO = standardSubjects.get(i);
            XSSFRow row = spreadsheet.createRow(startIndex);
            XSSFCell cell = row.createCell(0);

            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            XSSFFont fontTitle = workbook.createFont();
            fontTitle.setFontHeightInPoints((short) 12); // 字体大小
            fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
            fontTitle.setFontName("宋体");
            fontTitle.setBold(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cellStyle.setFont(fontTitle);
            cell.setCellStyle(cellStyle);

            cell.setCellValue(singleExportDTO.getMainName());
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(singleExportDTO.getSubName());
            startIndex++;
        }
        int mergeIndex = 3;
        for (int i = 0; i < mainCodeList.size(); i++) {
            int size = mianCoedSizeMap.get(mainCodeList.get(i)).size();
            CellRangeAddress cellRangeAddress = new CellRangeAddress(mergeIndex, mergeIndex + size - 1, 0, 0);
            spreadsheet.addMergedRegion(cellRangeAddress);
            mergeIndex += size;
        }
    }

    private void createMainMonth(XSSFSheet spreadsheet, XSSFWorkbook workbook) {
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 10); // 字体大小
        fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(fontTitle);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        XSSFRow rowMonth = spreadsheet.createRow(1);
        XSSFRow rowTitle = spreadsheet.createRow(2);
        XSSFCell cellMonth = rowMonth.createCell(0);
        CellRangeAddress cellRangeAddress1 = new CellRangeAddress(1, 1, 0, 1);
        spreadsheet.addMergedRegion(cellRangeAddress1);
        cellMonth.setCellValue("月份");
        cellMonth.setCellStyle(titleStyle);
        XSSFCell cellTitle = rowTitle.createCell(0);
        XSSFCell cellTitle1 = rowTitle.createCell(1);
        cellTitle.setCellStyle(titleStyle);
        cellTitle1.setCellStyle(titleStyle);
        cellTitle.setCellValue("总科目");
        cellTitle1.setCellValue("明细科目");
        List<String> subTitle = getSubTitle();
        List<String> subList = subTitle.subList(2, subTitle.size() - 1);
        for (int i = 0; i < subList.size(); i++) {
            XSSFCell cell1 = rowMonth.createCell(2 * i + 2);
            cell1.setCellValue(subList.get(i));
            CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 2 * i + 2, 2 * i + 3);
            spreadsheet.addMergedRegion(cellRangeAddress);
            XSSFCell cell2 = rowTitle.createCell(2 * i + 2);
            XSSFCell cell3 = rowTitle.createCell(2 * i + 3);
            cell2.setCellValue("实际");
            cell3.setCellValue("预算");
            cell1.setCellStyle(titleStyle);
            cell2.setCellStyle(titleStyle);
            cell3.setCellStyle(titleStyle);
        }
        //锁住前三行
        spreadsheet.createFreezePane(2, 3);
    }

    private void createMainTable(XSSFSheet spreadsheet, XSSFWorkbook workbook, String year) {
        XSSFRow row = spreadsheet.createRow((short) 0);
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 18); // 字体大小
        fontTitle.setColor(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap())); // 字体颜色
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(fontTitle);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 30);
        spreadsheet.addMergedRegion(cellRangeAddress);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(year.concat("年内部财务核算报表（主表）"));
        cell.setCellStyle(titleStyle);
        row.setHeightInPoints(24);
    }

    private void deleteColumns(XSSFSheet sheet, List<String> months) {
        List<String> delMonths = new ArrayList<>();
        HashMap<String, Integer> monthColIndex = new HashMap<>();
        for (int i = 1; i <= 12; i++) {
            if (!months.contains(i + "")) {
                delMonths.add(i + "");
            }
            monthColIndex.put(i + "", i + 1);
        }
        for (String month : delMonths) {
            int colIndex = monthColIndex.get(month);
            ExportUtil.deleteColumn(sheet, colIndex);
            for (int i = Integer.parseInt(month) + 1; i <= 12; i++) {
                monthColIndex.put(i + "", monthColIndex.get(i + "") - 1);
            }
        }
    }

    //  添加批注
    private void setCellComment(XSSFSheet sheet, XSSFWorkbook workbook, Integer rowIndex, Integer columnIndex, String commentStr) {
        XSSFDrawing draw = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置
        XSSFComment comment = draw.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex, columnIndex + 5, rowIndex + 5));
        XSSFRichTextString rtf = new XSSFRichTextString(commentStr);
        XSSFFont commentFormatter = workbook.createFont();
        commentFormatter.setFontName("宋体");
        //设置字体大小
        commentFormatter.setFontHeightInPoints((short) 9);
        rtf.applyFont(commentFormatter);
        comment.setString(rtf);
        //comment.setAuthor("Author");
        // 将批注添加到单元格对象中'
        sheet.getRow(rowIndex).getCell(columnIndex).setCellComment(comment);
    }

    // 添加本年合计计算公式
    private void setYearTotal(XSSFSheet spreadsheet, XSSFWorkbook workbook, Integer rowNum, Integer monthNum,
                              Map<String, XSSFCellStyle> styleMap, XSSFCellStyle defaultStyle) {
        String az = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        List<String> colIndex = new ArrayList<>();
        for (int j = 0; j < az.length(); j++) {
            colIndex.add(az.substring(j, j + 1));
        }
        for (int j = 0; j < az.length(); j++) {
            colIndex.add("A" + az.charAt(j));
        }
        int from = 2;
        int end = 2 + monthNum - 1;
        for (int i = 2; i < rowNum; i++) {
            XSSFRow row = spreadsheet.getRow(i);
            // if (row == null) continue;
            String name = row.getCell(1).getStringCellValue();
            XSSFCell cell = row.createCell(end + 2);
            String formula = "SUM(" + colIndex.get(from) + (i + 1) + ":" + colIndex.get(end + 1) + (i + 1) + ")";
            cell.setCellFormula(formula);
            cell.setCellStyle(styleMap.getOrDefault(name, defaultStyle));
        }
    }

    //最后行按照导出类型添加批注
    private void setLastComment(XSSFSheet spreadsheet, XSSFWorkbook workbook, String comment, Integer startRow, Integer lastCol) {
        // "备注"字体样式
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 12); // 字体大小
        fontTitle.setColor(new XSSFColor(new java.awt.Color(167, 51, 29), new DefaultIndexedColorMap())); // 字体颜色
        fontTitle.setFontName("宋体");
        fontTitle.setBold(true);
        // "备注"框样式
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFont(fontTitle);
        titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
        titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
        titleStyle.setBorderTop(BorderStyle.THIN);//上边框
        titleStyle.setBorderRight(BorderStyle.THIN);//右边框
        titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap()));
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 备注内容字体样式
        XSSFFont fontComment = workbook.createFont();
        fontComment.setFontHeightInPoints((short) 11); // 字体大小
        fontComment.setColor(new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap())); // 字体颜色
        fontComment.setFontName("宋体");
        // "备注"框样式
        XSSFCellStyle commentStyle = workbook.createCellStyle();
        commentStyle.setAlignment(HorizontalAlignment.LEFT);
        commentStyle.setVerticalAlignment(VerticalAlignment.TOP);
        commentStyle.setFont(fontComment);
        commentStyle.setBorderBottom(BorderStyle.THIN); //下边框
        commentStyle.setBorderLeft(BorderStyle.THIN);//左边框
        commentStyle.setBorderTop(BorderStyle.THIN);//上边框
        commentStyle.setBorderRight(BorderStyle.THIN);//右边框
        commentStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap()));
        commentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        commentStyle.setWrapText(true);

        XSSFRow row = spreadsheet.createRow(startRow);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("备注");
        cell.setCellStyle(titleStyle);
        for(int i=1; i<=lastCol; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(commentStyle);
        }
        CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, startRow, 1, lastCol);
        spreadsheet.addMergedRegion(cellRangeAddress);
        cell = row.getCell(1);
        cell.setCellValue(comment);
        cell.setCellStyle(commentStyle);
        row.setHeightInPoints(72);
    }
}
